﻿using System.Text;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WebAppEF.Models;
using X.PagedList;

namespace WebAppEF.Controllers
{
    public class Chp03Controller : Controller
    {
        DbtestContext db=new DbtestContext();
        public IActionResult Demo01()
        {
            //linq语法
            var listView = from emp in db.Employees
                           join dept in db.Depts on emp.DeptId equals dept.DeptId
                           select new
                           {
                               EmpId = emp.EmpId,
                               DeptId = emp.DeptId,
                               DeptName = dept.DeptName,
                               EmpName = emp.EmpName,
                               EmpPhone = emp.EmpPhone,
                               EmpArea = emp.EmpArea,
                               EmpSalary = emp.EmpSalary,
                           };
            ViewBag.listView=listView;
            return View();
        }

        public IActionResult AddForm()
        {         
            ViewBag.listDept=db.Depts.ToList();
            return View();
        }

        public IActionResult Add()
        {
            //方案一、EF框架读写数据库
           //Employee emp = new Employee();//创建实体
           // emp.DeptId = int.Parse(Request.Form["DeptId"].ToString());
           // emp.EmpName = Request.Form["txtRealName"].ToString();
           // emp.EmpPhone = Request.Form["txtPhone"].ToString();
           // emp.EmpArea = Request.Form["txtArea"].ToString();
           // emp.EmpSalary = decimal.Parse(Request.Form["txtSalary"].ToString());
           // db.Employees.Add(emp);
           // db.SaveChanges();
           //

            //方案二、直接通过SQL语句读写数据库
            Employee emp = new Employee();//创建实体
            emp.DeptId = int.Parse(Request.Form["DeptId"].ToString());
            emp.EmpName = Request.Form["txtRealName"].ToString();
            emp.EmpPhone = Request.Form["txtPhone"].ToString();
            emp.EmpArea = Request.Form["txtArea"].ToString();
            emp.EmpSalary = decimal.Parse(Request.Form["txtSalary"].ToString());
            db.Database.ExecuteSqlInterpolated(@$"insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary) values({emp.DeptId},{emp.EmpName},{emp.EmpPhone},{emp.EmpArea},{emp.EmpSalary})");

             return Content("<script>alert('添加成功!');window.location.href='AddForm';</script>", "text/html", Encoding.UTF8);
        }

        public IActionResult EditForm()
        {
            //查询所有部门
            ViewBag.listDept=db.Depts.ToList();
            //查询员工详情
            ViewBag.emp = db.Employees.Find(int.Parse(Request.Query["EmpId"].ToString()));
            return View();
        }

        /// <summary>
        /// 数据的编辑
        /// </summary>
        /// <returns></returns>
        public IActionResult Update()
        {
            //方案一 EF框架执行数据的编辑
            //int empId=int.Parse(Request.Form["hdEmpId"].ToString());//获取员工编号
            //Employee emp = db.Employees.Find(empId);
            //if (emp == null)
            //{
            //    return Content("<script>alert('找不到数据!');window.location.href='Demo01';</script>", "text/html", Encoding.UTF8);
            //}
            //emp.DeptId = int.Parse(Request.Form["DeptId"].ToString());
            //emp.EmpName = Request.Form["txtRealName"].ToString();
            //emp.EmpPhone = Request.Form["txtPhone"].ToString();
            //emp.EmpArea = Request.Form["txtArea"].ToString();
            //emp.EmpSalary = decimal.Parse(Request.Form["txtSalary"].ToString());
            //db.SaveChanges();

            //方案二 EF框架执行SQL语句的方式
            int empId = int.Parse(Request.Form["hdEmpId"].ToString());//获取员工编号
            int deptId = int.Parse(Request.Form["DeptId"].ToString());
            string empName = Request.Form["txtRealName"].ToString();
            string empPhone = Request.Form["txtPhone"].ToString();
            string empArea = Request.Form["txtArea"].ToString();
            decimal empSalary = decimal.Parse(Request.Form["txtSalary"].ToString());
            db.Database.ExecuteSqlInterpolated(@$"update Employee set DeptId={deptId},EmpName={empName},EmpPhone={empPhone},EmpArea={empArea},EmpSalary={empSalary} where EmpId={empId}");

            return Content("<script>alert('修改成功!');window.location.href='EditForm?EmpId="+empId+"';</script>", "text/html", Encoding.UTF8);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns></returns>
        public IActionResult Delete()
        {
            //EF框架执行删除操作
            int empId = int.Parse(Request.Query["EmpId"].ToString());
            Employee? emp = db.Employees.Find(empId);
            if (emp == null)
                return Content("<script> alert('找不到数据');window.location.href='Demo01';</script>", "text/html", Encoding.UTF8);
            db.Employees.Remove(emp);
            db.SaveChanges();

            //EF框架执行SQL语句
            //int empId = int.Parse(Request.Query["EmpId"].ToString());
            //int rowCount = db.Database.ExecuteSqlInterpolated($@"delete from Employee where EmpId ={empId}");

            return Content("<script>alert('删除成功!');window.location.href='Demo01';</script>", "text/html", Encoding.UTF8);
        }

        /// <summary>
        /// EF+组合条件搜索+分页
        /// </summary>
        /// <returns></returns>
        public IActionResult SearchForm()
        {
            var listView = from emp in db.Employees
                           join dept in db.Depts on emp.DeptId equals dept.DeptId
                           select new
                           {
                               EmpId = emp.EmpId,
                               DeptId = emp.DeptId,
                               DeptName = dept.DeptName,
                               EmpName = emp.EmpName,
                               EmpPhone = emp.EmpPhone,
                               EmpArea = emp.EmpArea,
                               EmpSalary = emp.EmpSalary,
                           };
            //添加查询条件--部门
            if (!string.IsNullOrEmpty(Request.Query["ddlDept"]))
            {
                int deptId = int.Parse(Request.Query["ddlDept"].ToString());
                listView=listView.Where(p=>p.DeptId == deptId);
            }

            //添加查询条件--姓名
            if (!string.IsNullOrEmpty(Request.Query["txtRealName"]))
            {
                string realName = Request.Query["txtRealName"].ToString();
                listView = listView.Where(p => p.EmpName.Contains(realName));
            }

            //处理分页（页码大小——一页显示几行、当前页码、总页数）
            int pageSize = 2;
            int pageIndex = 1;
            if (!string.IsNullOrEmpty(Request.Query["page"]))
                pageIndex=int.Parse(Request.Query["page"].ToString());
            int recordCount=listView.Count();//总行数
            int pageCount = recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1;
            //如果除尽，页数=总行数÷页面大小
            //如果除不尽，页数=总行数÷页面大小+1

            if(pageIndex>pageCount) pageIndex= pageCount;
            if(pageIndex<1) pageIndex=1;

            listView = listView.OrderBy(p => p.EmpId).Skip(pageSize * (pageIndex - 1)).Take(pageSize);
            ViewBag.listView = listView;

            //所有部门信息
            ViewBag.listDept=db.Depts.ToList();
            //分页
            ViewBag.pageSize = pageSize;
            ViewBag.pageIndex = pageIndex;
            ViewBag.pageCount = pageCount;
            
            return View();
        }

        /// <summary>
        /// 使用PagedList插件实现分页
        /// </summary>
        /// <returns></returns>
        public IActionResult PagedListForm(int page=1)
        {
            var listView = from emp in db.Employees
                           join dept in db.Depts on emp.DeptId equals dept.DeptId
                           select new
                           {
                               EmpId = emp.EmpId,
                               DeptId = emp.DeptId,
                               DeptName = dept.DeptName,
                               EmpName = emp.EmpName,
                               EmpPhone = emp.EmpPhone,
                               EmpArea = emp.EmpArea,
                               EmpSalary = emp.EmpSalary,
                           };
            //添加查询条件--部门
            if (!string.IsNullOrEmpty(Request.Query["ddlDept"]))
            {
                int deptId = int.Parse(Request.Query["ddlDept"].ToString());
                listView = listView.Where(p => p.DeptId == deptId);
            }

            //添加查询条件--姓名
            if (!string.IsNullOrEmpty(Request.Query["txtRealName"]))
            {
                string realName = Request.Query["txtRealName"].ToString();
                listView = listView.Where(p => p.EmpName.Contains(realName));
            }

            int pageSize = 6;
            var data = listView.OrderByDescending(p => p.EmpId).ToPagedList(page,pageSize);
            ViewBag.ListDept = db.Depts;
            ViewBag.DataList= data;

            return View();
        }

        /// <summary>
        /// EF中执行复杂SQL查询操作
        /// </summary>
        /// <returns></returns>
        public IActionResult SqlQueryForm()
        {
            //查询数量
            var cnt=db.Database.SqlQuery<int>($"select count(*)from Employee where EmpArea='武汉'").ToList();
            ViewBag.cnt=cnt.FirstOrDefault();

            //单表查询
            var query1 = db.Employees.FromSqlInterpolated($"select * from Employee where EmpArea='武汉'");
            ViewBag.listView1=query1;

            return View();
        }


        /// <summary>
        /// 使用EF Core框架实体类本身的关系关联来进行关联数据的查询
        /// </summary>
        /// <returns></returns>
        public IActionResult SqlJoinForm()
        {
            //案例一
            var listView = from emp in db.Employees.Include(p => p.TheDept)
                           select emp;

            //案例二
            //var listView = db.Employees.FromSqlInterpolated($"select * from Employee where EmpArea='武汉'").Include(p => p.TheDept);


            ViewBag.listView = listView;
            return View();
        }
    
    }
}
